﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Configuration;

namespace Databases
{
    /// <summary>
    /// This is the Base DAL for the databases class. Provides all the functionality
    /// to retrieve insert or update data
    /// </summary>
    class DAL
    {
        /// <summary>
        /// The string name for the Access databse connection
        /// </summary>
        private string connStrName = "Databases.Properties.Settings.DatabaseAccess0ConnectionString";

        /// <summary>
        /// Returns the filled dataset based on the passed query.
        /// </summary>
        /// <param name="query">The query to use for the dataset</param>
        /// <returns>DataSet with the data from the passed query</returns>
        public DataSet getDataSet(string query)
        {
            using(OleDbConnection conn = new OleDbConnection())
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings[connStrName].ToString();
                conn.Open();

                return FillDataSet(query, conn);
            }
        }

        /// <summary>
        /// Gets a DataSet bassed on the passed query that accepts the passed list of
        /// OleDbParameters.
        /// </summary>
        /// <param name="query">The query to use for the dataset that also accepts parameters</param>
        /// <param name="parameters">The list of parameters that are used in the passed query</param>
        /// <returns>DataSet with the data from the passed query</returns>
        public DataSet getDataSetWithParameters(string query, List<OleDbParameter> parameters)
        {
            using (OleDbConnection conn = new OleDbConnection())
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings[connStrName].ToString();
                conn.Open();

                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = conn;
                cmd.CommandText = query;
                cmd.CommandType = CommandType.Text;

                foreach (OleDbParameter p in parameters)
                {
                    cmd.Parameters.Add(p);
                }

                return FillDataSet(cmd);
            }
        }

        /// <summary>
        /// Updates the database with the passed update statement using the parameters list
        /// to set the values that will be updated.
        /// </summary>
        /// <param name="query">The update statement sql to use</param>
        /// <param name="parameters">The parameters list that has the values to insert into the statement</param>
        /// <returns>The number of rows affected as an int</returns>
        public int updateData(string query, List<OleDbParameter> parameters)
        {
            using (OleDbConnection conn = new OleDbConnection())
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings[connStrName].ToString();
                conn.Open();

                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = conn;
                cmd.CommandText = query;
                cmd.CommandType = CommandType.Text;

                foreach (OleDbParameter p in parameters)
                {
                    cmd.Parameters.Add(p);
                }
                int rowsAffected = cmd.ExecuteNonQuery();
                return rowsAffected;
            }
        }

        /// <summary>
        /// Inserts data into the database
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="parameters">The List of parameters to use with the query, can be empty if the query doesn't require parameters</param>
        /// <param name="identityQuery">Pass an empty string if you don't need an identity value returned. Otherwise pass the Identity query</param>
        /// <returns>A NonQueryResult object that has the RowsAffected set along with an Identy if identityQuery was passed</returns>
        public NonQueryResult insertData(string query, List<OleDbParameter> parameters, string identityQuery)
        {
            using (OleDbConnection conn = new OleDbConnection())
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings[connStrName].ToString();
                conn.Open();

                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = conn;
                cmd.CommandText = query;
                cmd.CommandType = CommandType.Text;

                foreach (OleDbParameter p in parameters)
                {
                    cmd.Parameters.Add(p);
                }
                // Setup the NonQueryResult
                NonQueryResult nqr = new NonQueryResult();
                nqr.RowsAffected = cmd.ExecuteNonQuery();

                // if the identityQuery is not empty then set it to the command
                // and execute it to get the last insert identity.
                if (!identityQuery.Equals(string.Empty))
                {
                    cmd.CommandText = identityQuery;
                    cmd.Parameters.Clear();
                    //store the identity in the NonQueryResult
                    nqr.Identity = (int)cmd.ExecuteScalar();
                }
                return nqr;
            }
        }

        /// <summary>
        /// Utility method with 2 overloads. This fills the data set using a passed query and a conn
        /// </summary>
        /// <param name="query">Query to use to fill the dataset</param>
        /// <param name="conn">The connection object that will be used for the connection.</param>
        /// <returns>The filled dataset</returns>
        private DataSet FillDataSet(string query, OleDbConnection conn)
        {
            OleDbDataAdapter da = new OleDbDataAdapter(query, conn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds;
        }

        /// <summary>
        /// Utility method with 2 overloads. This fills the data set using a passed OleDbCommand object
        /// </summary>
        /// <param name="cmd">The OleDbCommand to use with the OleDbDataAdapter</param>
        /// <returns>The filled dataset</returns>
        private DataSet FillDataSet(OleDbCommand cmd)
        {
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds;
        }
    }
}
